package nus.iss.laps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import nus.iss.laps.cri.Criteria;
import nus.iss.laps.model.Holiday;

public class HolidayDAO extends DAO implements IHolidayDAO, RowMapper<Holiday> {
	
	public HolidayDAO()
	{
		table = "holiday";
	}
	
	@Override
	public boolean addHoliday(Holiday holiday) throws SQLException {
		query = "insert into " + table + " values(?, ?)";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query, holiday.getHoliday(), holiday.getDescription()) > 0;
	}

	@Override
	public boolean updateHoliday(Holiday holiday) throws SQLException {
		query =  "update " + table + " set " +
				"description = ? " +
				"where holiday = '" + holiday.getHoliday() + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query, holiday.getDescription()) > 0;
	}

	@Override
	public boolean deleteHoliday(String holiday) throws SQLException {
		query = getDeleteQuery() + " where holiday = '" + holiday + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query) > 0;
	}

	@Override
	public Holiday getHoliday(String holiday) throws SQLException {
		query = getSelectQuery() + " where holiday = '" + holiday + "'";
		try {
			jdbcTemplate = new JdbcTemplate(dataSource);
			return jdbcTemplate.queryForObject(query, this);
		} catch (IncorrectResultSizeDataAccessException e) {
			return null;
		}
	}

	@Override
	public List<Holiday> getAllHoliday() throws SQLException {
		query = getSelectQuery();
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.query(query, this);
	}

	@Override
	public Holiday mapRow(ResultSet arg0, int arg1) throws SQLException {
		Holiday l_holiday = new Holiday();
	    l_holiday.setHoliday(arg0.getString("holiday"));
	    l_holiday.setDescription(arg0.getString("description"));
	      return l_holiday;
	}
	
	public List<Holiday> getHolidays(Criteria cri) throws SQLException{
		String l_cri = " (date_format(holiday, '%d/%m/%Y') like '%" + cri.getKeyword() + "%'"
				+ " or description like '%" + cri.getKeyword() + "%')";
		query = getSelectQuery()
				+ " where " + l_cri 
				+ " order by holiday desc"
				+ " limit " + cri.getRowIndex() + ", " + cri.getRowLimit();
		jdbcTemplate = new JdbcTemplate(dataSource);
		
		String l_queryCount = "select count(holiday) from " + table + " where " + l_cri;
		cri.setRecordCount(jdbcTemplate.queryForLong(l_queryCount));
		
		return jdbcTemplate.query(query, this);
	}

	@Override
	public boolean isExisted(String holiday) throws SQLException {
		if(getHoliday(holiday) != null)
			return true;
		return false;
	}

	@Override
	public int getDays(String startDate, String endDate) throws SQLException {
		query = "select count(holiday) from " + table + " where holiday >= '" + startDate + "' and holiday <= '" + endDate + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.queryForInt(query);
	}

	@Override
	public List<Holiday> getHolidaysByMonth(String month) throws SQLException {
		query = getSelectQuery() + " where holiday like '" + month + "%'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.query(query, this);
	}
}
